<?php
/**
 * Created by PhpStorm.
 * User: Dinh Thi
 * Date: 2/23/15
 * Time: 9:46 PM
 */

namespace MyApp\Application\Model;

use DinhThi\MVC\Model\MySQLModel;
class ChiTieuModel extends MySQLModel{
    public function __construct(){
        parent::connect();
    }

    public function ThemChiTieu($sotien, $lydo){
        $sth = $this->DB->prepare("insert into chitieu (sotien, lydo) values (:sotien, :lydo)");
        $sth->execute(array(
            ':sotien'   => $sotien,
            ':lydo'     => $lydo
        ));


        $sth = $this->DB->prepare('select LAST_INSERT_ID() as ID');
        $sth->execute();
        $id = $sth->fetch();
        $id = $id['ID'];
        return array('id' => $id, 'sotien' => $sotien, 'lydo' => $lydo);
    }

    public function DanhSachChiTieu($begin, $end){
        $sth = $this->DB->prepare("SELECT * FROM `chitieu` WHERE UNIX_TIMESTAMP(thoigian) > UNIX_TIMESTAMP(:begin) and UNIX_TIMESTAMP(thoigian) < UNIX_TIMESTAMP(:end)");
        $sth->execute(array(
            ':begin'    => $begin,
            ':end'      => $end
        ));

        return $sth->fetchAll();
    }

    public function ChiTieuTheoNgay($begin, $end){
        $sth = $this->DB->prepare('SELECT DATE_FORMAT(thoigian, \'%d/%m/%Y\') AS Ngay,
                                    SUM(sotien) AS TongChi
                                    FROM   `chitieu`
                                    WHERE DATE(thoigian) >= DATE :begin AND DATE(thoigian) <= DATE :end
                                    GROUP BY DATE(thoigian)
                                    ORDER BY Ngay
                                   ');

        $sth->execute(array(
            ':begin'    => $begin,
            ':end'      => $end
        ));

        return $sth->fetchAll();
    }

    public function ChiTieuTheoThang($begin, $end){
        $sth = $this->DB->prepare('SELECT DATE_FORMAT(thoigian, \'%m/%Y\') AS Ngay,
                                    SUM(sotien) AS TongChi
                                    FROM   `chitieu`
                                    WHERE MONTH(DATE(thoigian)) >= MONTH (DATE :begin) AND MONTH(DATE(thoigian)) <= MONTH (DATE :end)
                                    GROUP BY MONTH (DATE(thoigian))
                                    ORDER BY Ngay
                                   ');

        $sth->execute(array(
            ':begin'    => $begin,
            ':end'      => $end
        ));

        return $sth->fetchAll();
    }
} 